package modele;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.restlet.data.MediaType;
import org.restlet.ext.xml.DomRepresentation;
import org.restlet.representation.Representation;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 * DatabaseManager
 * @author Théo
 */
public class DatabaseManager {

    /**
     * Retourne la liste des tables de la base de données
     * @return Les tables de la base de données
     */
    public static List<String> getLstTables() {
        List<String> lstTables = new ArrayList<String>();
        Connection connexion = Database.getInstance().getConnection();
        ResultSet rs = null;

        try {
            DatabaseMetaData dbmd = connexion.getMetaData();
            rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});

            while (rs.next()) {
                lstTables.add(rs.getString("TABLE_NAME"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return lstTables;
    }

    /**
     * Retourne la liste des colonnes pour une table donnée
     * @param table La table dont on recherche les colonnes
     * @return La liste des colonnes de la table recherchée
     */
    public static List<String> getLstColumns(String table) {
        List<String> lstTables = new ArrayList<String>();
        Connection connexion = Database.getInstance().getConnection();
        ResultSet rs = null;

        try {
            Statement st = connexion.createStatement();
            rs = st.executeQuery("SELECT * FROM " + table);
            ResultSetMetaData md = rs.getMetaData();

            for (int i = 1; i <= md.getColumnCount(); ++i) {
                lstTables.add(md.getColumnName(i));
            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return lstTables;
    }

    /**
     * Effectue une requête standard de sélection sur une colonne
     * @param table La table sur laquelle effectuer la sélection
     * @param col Le nom de la colonne à sélectionner
     * @return Le contenu de la colonne
     */
    public static List<String> getTableByField(String table, String col) {
        List<String> lstLines = new ArrayList<String>();
        Connection connexion = Database.getInstance().getConnection();
        ResultSet rs = null;

        try {
            String sql = "SELECT " + col + " FROM " + table;
            Statement stmt = connexion.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs != null && rs.next()) {
                lstLines.add(rs.getString(col));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            Database.getInstance().close(connexion);
        }

        return lstLines;
    }

    public static List<String> getDatabases() {
        List<String> lstDatabases = new ArrayList<String>();

        ResultSet rs;
        try {
            rs = Database.getInstance().getConnection().getMetaData().getCatalogs();
            while (rs.next()) {
                lstDatabases.add(rs.getString("TABLE_CAT"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return lstDatabases;
    }

    public static Representation getRows(String table) {
        Connection connexion = Database.getInstance().getConnection();
        ResultSet rs = null;
        DomRepresentation dom = null;
        try {
            dom = new DomRepresentation(MediaType.TEXT_XML);
            // Generer un DOM representant la ressource
            Document doc = dom.getDocument();
            Element root = doc.createElement("database");
            root.setAttribute("name", Database.getDb());
            doc.appendChild(root);
            Element tb = doc.createElement("table");
            tb.setAttribute("name", table);
            root.appendChild(tb);

            String sql = "SELECT * FROM " + table;
            Statement stmt;
            stmt = connexion.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int nbCol = rsmd.getColumnCount();
            Integer id = 1;
            Element structure = doc.createElement("structure");
            for (int i = 1; i <= nbCol; ++i) {
                Element column = doc.createElement("column");
                column.setAttribute("name", rsmd.getColumnName(i));
                column.setAttribute("class", rsmd.getColumnClassName(i));
                column.setAttribute("type", rsmd.getColumnTypeName(i));
                structure.appendChild(column);
            }
            tb.appendChild(structure);
            while (rs != null && rs.next()) {
                Element row = doc.createElement("row");
                row.setAttribute("id", id.toString());
                ++id;
                for (int i = 1; i <= nbCol; ++i) {
                    Object record = rs.getObject(i);
                    Element field = doc.createElement("field");
                    field.setAttribute("name", rsmd.getColumnName(i));
                    field.setTextContent(record.toString());
                    row.appendChild(field);
                }
                tb.appendChild(row);
            }
        } catch (IOException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return dom;
    }

    public static Representation getColumns(String table) {
        Connection connexion = Database.getInstance().getConnection();
        ResultSet rs = null;
        DomRepresentation dom = null;
        try {
            dom = new DomRepresentation(MediaType.TEXT_XML);
            // Generer un DOM representant la ressource
            Document doc = dom.getDocument();
            Element root = doc.createElement("database");
            root.setAttribute("name", Database.getDb());
            doc.appendChild(root);
            Element tb = doc.createElement("table");
            tb.setAttribute("name", table);
            root.appendChild(tb);

            for (int i = 0; i < getLstColumns(table).size(); ++i) {
                Element column = doc.createElement("column");
                column.setAttribute("name", getLstColumns(table).get(i));
                tb.appendChild(column);
            }
        } catch (IOException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return dom;
    }

    public static String getUserPwdByLogin(String login){
        String pwd = null;
        Database.setConnectionData("", "root", "");
        Connection connexion = Database.getInstance().getConnection();
        ResultSet rs = null;

        try {
            String sql = "SELECT password FROM mysql.user where user = '" + login + "'";
            Statement stmt = connexion.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs != null && rs.next()) {
                pwd = rs.getString("password");
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            Database.getInstance().close(connexion);
        }

        return pwd;
    }
}
